﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using DatabaseCompare.Model;
using DatabaseCompare.Service.Reader;

namespace DatabaseCompare.ServiceImpl.Reader
{
    

    /// <summary>
    /// SQL Server 2005 数据库信息读取
    /// 
    /// <remarks> 这个类是一开始写的，以后使用 Sql2005DatabaseInfoReader 那个类来读取. </remarks>
    /// </summary>
    public class SqlServer2005DatabaseInfoReader : IDatabaseInfoReader
    {

//        select 
//  v.name,
//  c.text
//from 
//sys.views  v LEFT OUTER JOIN
//       dbo.syscomments c ON v.object_id = c.id


        #region 读取表/字段信息的SQL语句



        /// <summary>
        /// 读取表信息的 SQL
        /// </summary>
        private const string READ_TABLE_INFO_SQL = @"select
  a.name AS TableName,
  isnull(g.[value],'-') AS Comment
from
  sys.tables a left join sys.extended_properties g
  on (a.object_id = g.major_id AND g.minor_id = 0)
";



        /// <summary>
        /// 读取列信息的 SQL
        /// </summary>
        private const string READ_COLUMN_INFO_SQL = @"SELECT
   col.name AS ColumnName,
   col.column_id  AS  ColumnId,
   typ.name as DataType,
   col.max_length AS MaxLength,
   col.precision AS Precision,
   col.Scale AS Scale,
   col.is_nullable  AS IsNullAble,
   col.is_identity  AS IsIDEntity,
   case when exists 
      ( SELECT 1 
        FROM 
          sys.indexes idx 
            join sys.index_columns idxCol 
            on (idx.object_id = idxCol.object_id)
         WHERE
            idx.object_id = col.object_id
            AND idxCol.index_column_id = col.column_id
            AND idx.is_primary_key = 1
       ) THEN 1 ELSE 0 END  AS IsPrimaryKey,
  isnull(prop.[value],'-') AS Comment
FROM
  sys.columns col 
    left join sys.types typ 
      on (col.system_type_id = typ.system_type_id)
    left join sys.extended_properties prop
      on (col.object_id = prop.major_id AND prop.minor_id = col.column_id)
WHERE
  col.object_id =
    (SELECT object_id FROM sys.tables WHERE name = @table_name)
";



        /// <summary>
        /// 读取
        /// </summary>
        private const string READ_VIEW_INFO_SQL = @"select
  a.name AS ViewName,
  '-' AS Comment,
  c.text AS CreateViewSQL
from
  sys.views a 
  LEFT OUTER JOIN
       dbo.syscomments c ON a.object_id = c.id";


        #endregion






        #region IDatabaseInfoReader 成员


        /// <summary>
        /// 连接字符串.
        /// </summary>
        private string connectString;


        /// <summary>
        /// 数据库名称.
        /// </summary>
        private string databaseName;



        public string ConnectString
        {
            get
            {
                return connectString;
            }
            set
            {
                connectString = value;
            }
        }

        public string DatabaseName
        {
            get
            {
                return databaseName;
            }
            set
            {
                databaseName = value;
            }
        }


        /// <summary>
        /// 读取.
        /// </summary>
        /// <returns></returns>
        public DatabaseInfo ReadDatabaseInfo()
        {
            DatabaseInfo result = new DatabaseInfo();
            result.Name = databaseName;
            result.TableList = new List<TableInfo>();


            // 建立数据库连接.
            SqlConnection conn = new SqlConnection(connectString);

            // 打开连接.
            conn.Open();

            // 创建一个 Command.
            SqlCommand testCommand = conn.CreateCommand();

            // 定义需要执行的SQL语句.
            testCommand.CommandText = READ_TABLE_INFO_SQL;

            // 执行SQL命令，结果存储到Reader中.
            SqlDataReader testReader = testCommand.ExecuteReader();

            // 处理检索出来的每一条数据.
            while (testReader.Read())
            {
                // 依次处理每一个表.
                TableInfo tableInfo = new TableInfo();

                // 表名.
                tableInfo.Name = testReader["TableName"].ToString();
                // 备注.
                tableInfo.Comment = testReader["Comment"].ToString();


                // 处理完毕后，表加入结果列表.
                result.TableList.Add(tableInfo);
            }

            // 关闭Reader.
            testReader.Close();


            SqlCommand colCommand = conn.CreateCommand();
            colCommand.CommandText = READ_COLUMN_INFO_SQL;
            // 定义要查询的参数.
            SqlParameter sqlParam = new SqlParameter("@table_name", SqlDbType.VarChar);
            colCommand.Parameters.Add(sqlParam);


            // 所有的表信息处理完毕后，开始处理列信息.
            foreach (TableInfo tableInfo in result.TableList)
            {
                // 填写参数的值.
                sqlParam.Value = tableInfo.Name;

                // 执行SQL命令，结果存储到Reader中.
                SqlDataReader colReader = colCommand.ExecuteReader();


                // 初始化 表里面的列 List
                tableInfo.SubList = new List<ColumnInfo>();

                // 处理检索出来的每一条数据.
                while (colReader.Read())
                {
                    // 读取每一列.
                    ColumnInfo columnInfo = new ColumnInfo();


                    // 列名
                    columnInfo.Name = colReader["ColumnName"].ToString();
                    // 列的顺序
                    columnInfo.ColumnId = Convert.ToInt32(colReader["ColumnId"]);
                    // 数据类型
                    columnInfo.DataType = colReader["ColumnName"].ToString();
                    // 占用字节数
                    columnInfo.MaxLength = Convert.ToInt32(colReader["MaxLength"]);
                    // 长度
                    columnInfo.Precision = Convert.ToInt32(colReader["Precision"]);
                    // 小数位数
                    columnInfo.Scale = Convert.ToInt32(colReader["Scale"]);
                    // 是否允许非空
                    columnInfo.IsNullAble = Convert.ToInt32(colReader["IsNullAble"]) == 1; 
                    // 是否是自增ID
                    columnInfo.IsIDEntity = Convert.ToInt32(colReader["IsIDEntity"]) == 1;
                    // 是否是主键
                    columnInfo.IsPrimaryKey = Convert.ToInt32(colReader["IsPrimaryKey"]) == 1;
                    // 备注/说明信息
                    columnInfo.Comment = colReader["Comment"].ToString();

                    // 列加入表的列List.
                    tableInfo.SubList.Add(columnInfo);
                }

                // 关闭Reader.
                colReader.Close();
            }




            // 关闭数据库连接.
            conn.Close();


            // 返回.
            return result;
        }

        #endregion
    }
}
